package com.lifeverse.repository;

import com.lifeverse.entity.HealthCheck;
import com.lifeverse.entity.enums.HealthStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 健康检查数据访问层
 */
@Repository
public interface HealthCheckRepository extends JpaRepository<HealthCheck, Long> {

    /**
     * 根据服务名称查询健康检查
     */
    List<HealthCheck> findByServiceName(String serviceName);

    /**
     * 根据服务名称和检查名称查询
     */
    Optional<HealthCheck> findByServiceNameAndCheckName(String serviceName, String checkName);

    /**
     * 根据健康状态查询
     */
    List<HealthCheck> findByStatus(HealthStatus status);

    /**
     * 查询启用的健康检查
     */
    List<HealthCheck> findByEnabledTrue();

    /**
     * 查询不健康的服务
     */
    @Query("SELECT h FROM HealthCheck h WHERE h.status NOT IN ('HEALTHY', 'WARNING') AND h.enabled = true ORDER BY h.checkTime DESC")
    List<HealthCheck> findUnhealthyServices();

    /**
     * 查询需要检查的服务（基于检查间隔）
     */
    @Query("SELECT h FROM HealthCheck h WHERE h.enabled = true AND " +
           "(h.checkTime IS NULL OR h.checkTime < :threshold)")
    List<HealthCheck> findServicesDueForCheck(@Param("threshold") LocalDateTime threshold);

    /**
     * 根据服务名称获取最新的健康检查结果
     */
    @Query("SELECT h FROM HealthCheck h WHERE h.serviceName = :serviceName ORDER BY h.checkTime DESC")
    Page<HealthCheck> findLatestByServiceName(@Param("serviceName") String serviceName, Pageable pageable);

    /**
     * 统计各状态的健康检查数量
     */
    @Query("SELECT h.status, COUNT(h) FROM HealthCheck h WHERE h.enabled = true GROUP BY h.status")
    List<Object[]> countByStatus();

    /**
     * 统计各服务的健康状态
     */
    @Query("SELECT h.serviceName, h.status, COUNT(h) FROM HealthCheck h WHERE h.enabled = true GROUP BY h.serviceName, h.status")
    List<Object[]> countByServiceAndStatus();

    /**
     * 查询连续失败次数超过阈值的检查
     */
    @Query("SELECT h FROM HealthCheck h WHERE h.consecutiveFailures >= :threshold AND h.enabled = true ORDER BY h.consecutiveFailures DESC")
    List<HealthCheck> findByConsecutiveFailuresGreaterThanEqual(@Param("threshold") Integer threshold);

    /**
     * 查询响应时间超过阈值的检查
     */
    @Query("SELECT h FROM HealthCheck h WHERE h.responseTimeMs > :threshold AND h.enabled = true ORDER BY h.responseTimeMs DESC")
    List<HealthCheck> findByResponseTimeGreaterThan(@Param("threshold") Long threshold);

    /**
     * 获取服务健康趋势数据
     */
    @Query("SELECT DATE(h.checkTime) as date, h.status, COUNT(h) as count " +
           "FROM HealthCheck h " +
           "WHERE h.serviceName = :serviceName AND h.checkTime >= :since " +
           "GROUP BY DATE(h.checkTime), h.status " +
           "ORDER BY date DESC")
    List<Object[]> getHealthTrendByService(@Param("serviceName") String serviceName, @Param("since") LocalDateTime since);

    /**
     * 获取整体健康趋势
     */
    @Query("SELECT DATE(h.checkTime) as date, AVG(h.healthScore) as avgScore " +
           "FROM HealthCheck h " +
           "WHERE h.checkTime >= :since AND h.enabled = true " +
           "GROUP BY DATE(h.checkTime) " +
           "ORDER BY date DESC")
    List<Object[]> getOverallHealthTrend(@Param("since") LocalDateTime since);

    /**
     * 删除过期的健康检查记录
     */
    @Query("DELETE FROM HealthCheck h WHERE h.checkTime < :threshold")
    void deleteHealthChecksBefore(@Param("threshold") LocalDateTime threshold);

    /**
     * 获取服务的平均响应时间
     */
    @Query("SELECT h.serviceName, AVG(h.responseTimeMs) as avgResponseTime " +
           "FROM HealthCheck h " +
           "WHERE h.checkTime >= :since AND h.responseTimeMs IS NOT NULL AND h.enabled = true " +
           "GROUP BY h.serviceName " +
           "ORDER BY avgResponseTime DESC")
    List<Object[]> getAverageResponseTimeByService(@Param("since") LocalDateTime since);

    /**
     * 获取服务可用性统计
     */
    @Query("SELECT h.serviceName, " +
           "COUNT(CASE WHEN h.status IN ('HEALTHY', 'WARNING') THEN 1 END) * 100.0 / COUNT(h) as availability " +
           "FROM HealthCheck h " +
           "WHERE h.checkTime >= :since AND h.enabled = true " +
           "GROUP BY h.serviceName " +
           "ORDER BY availability DESC")
    List<Object[]> getServiceAvailability(@Param("since") LocalDateTime since);
}